﻿Imports knfUpdateDataLayer
Imports System.IO
Imports LibreriaNuovaExcel

Public Class excelHelper

    Private _reporter As String
    Private _aliasProduct As String
    Private _kindOfReport As String
    Private _listaAnni As New List(Of Integer)
    Private _isAveraged As Boolean
    Private _db As New knfEntities
    Private kPartnersImport As New List(Of String)
    Private kPartnersExport As New List(Of String)
    Private _filePath As String
    Private _imageFilePath As String

    Private nDebug As String
    Public Property myDebug() As String
        Get
            Return nDebug
        End Get
        Set(ByVal value As String)
            nDebug = value
        End Set
    End Property


    Public Sub New(ByVal Reporter As String, ByVal AliasProdotto As String, ByVal KindOfReport As String,
                   ByVal ListaAnni As List(Of Integer), ByVal isAveraged As Boolean)

        _reporter = Reporter
        _aliasProduct = AliasProdotto
        _kindOfReport = KindOfReport
        _listaAnni = ListaAnni
        _isAveraged = isAveraged



    End Sub

    Public Function CreaReport(ByVal filePath As String, ByVal imageFilePath As String) As String


        ' imagefilepath contiene anche il nome del png

        Try

            _filePath = filePath
            _imageFilePath = imageFilePath

            CreaListaImport("IMPORT")

            CreaListaImport("EXPORT")


            Dim nomeFile As String = Guid.NewGuid.ToString.Substring(0, 10)

            Dim PathFileExcel As String

            If _isAveraged = True Then
                PathFileExcel = filePath + "\Ave - " + nomeFile + ".xlsx"
            Else
                PathFileExcel = filePath + "\NoAve - " + nomeFile + ".xlsx"
            End If

            Purge(filePath)

            Dim tmpExcel As New generaReportExcelRevamped(PathFileExcel, _reporter, _aliasProduct, _kindOfReport, _listaAnni, kPartnersImport, kPartnersExport, imageFilePath, _isAveraged)

            If _reporter = "AUSTRIA" And _aliasProduct = "Rock mineral wool" And _kindOfReport = "Euro" Then

                Dim giga = 6
            End If


            tmpExcel.Genera()


            Return PathFileExcel

        Catch ex As Exception

            nDebug = kPartnersImport.Count.ToString + ex.Message

            Return nDebug

        End Try



    End Function


    Private Sub CreaListaImport(ByVal Flux As String)

        Dim SuperAnno As Integer = _listaAnni.Max - 1


        Dim import = (From p In _db.Tabella_Totali Where p.Reporter = _reporter _
                      And p.Flow = Flux And p.Product = _aliasProduct And
                      (p.Averaged = _isAveraged) And
                      (p.Year = SuperAnno) And p.Indicator = _kindOfReport Group By p.Partner
                     Into Total = Sum(p.Total) Order By Total Descending)


        If import.ToList.Count > 0 Then

            If Flux = "IMPORT" Then

                For Each tmp In import.ToList
                    kPartnersImport.Add(tmp.Partner)
                Next

            ElseIf Flux = "EXPORT" Then

                For Each tmp In import.ToList
                    kPartnersExport.Add(tmp.Partner)
                Next

            End If

        End If

    End Sub


    Private Sub Purge(ByVal InPath As String)

        Dim query = From o In Directory.GetFiles(InPath, "*.xlsx", SearchOption.AllDirectories) Select o

        For Each item As String In query.ToList

            If System.IO.File.GetCreationTime(item) <= System.DateTime.Now.AddHours(-5) Then

                File.Delete(item)

            End If

        Next

    End Sub

End Class
